


--************************************************
--YU_XML_TB   테이블 만들기
--************************************************

CREATE TABLE [dbo].[YU_XML_TB](
	[DT] [varchar](4) NOT NULL,
	[ME_ID] [varchar](10) NOT NULL,
	[YU_NO] [varchar](10) NOT NULL CONSTRAINT [DF_YU_XML_TB_YU_NO]  DEFAULT (''),
	[DU_XML] [xml] NULL,
 CONSTRAINT [PK_YU_XML_TB] PRIMARY KEY CLUSTERED 
(
	[DT] ASC,
	[ME_ID] ASC,
	[YU_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



GO


--************************************************
--XML 자료 저장하기
--************************************************
--EXEC X_YU_2015_XML '2016','2005-0216'
CREATE  PROC [dbo].[X_YU_2015_XML]
		  @DT VARCHAR(4)	
		 ,@ME_ID VARCHAR(10)
		 ,@YU_NO VARCHAR(10)		
AS 
SET NOCOUNT ON

--XML 자료를 저장하기 한다
			SELECT * 
			FROM YU_XML_TB
			WHERE DT=@DT
					AND ME_ID=@ME_ID	
					AND YU_NO=@YU_NO	

SET NOCOUNT OFF




GO



--select * from yu
--select * from yu_pdf_tb where dt='2015' and me_id='2005-0216'
--************************************************
--XML 자료를 나열하기
--************************************************
--EXEC X_YU_2015_XML;2 '2015','2005-0216','tttt'
CREATE  PROC [dbo].[X_YU_2015_XML];2
		  @DT VARCHAR(4)	
		 ,@ME_ID VARCHAR(10)	
		 ,@YU_NO VARCHAR(10)		
AS 
SET NOCOUNT ON

			DECLARE @XML XML
					,@ME_JU_NO VARCHAR(20)  --본인주민번호
			SELECT @ME_JU_NO =REPLACE ( DBO.fn_Me_JU_NO(@ME_ID),'-','')

			SELECT @XML = DU_XML
			FROM YU_XML_TB
			WHERE DT=@DT
					AND ME_ID=@ME_ID
					AND YU_NO=@YU_NO	


--********************************************************************************
--XML YU01   보험료  'YU01  [보험료]'
--********************************************************************************
--123456-1234567'
			
			SELECT   
					  @DT  AS DT
					, @ME_ID AS ME_ID
					,'YU01  [보험료]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드	 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid      --주민번호   --이름
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name       --사용자명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd     --자료코드 장애인 보장성 보험은 'G0002'
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid     --사업자번호 ' 1208132969
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm --상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no	  --증권번호
					, M.Item.query('./goods_nm').value('.', 'VARCHAR(100)') AS goods_nm  --보험종류  AS AMT		
					--, M.ITEM.value('./@goods_nm', 'VARCHAR(100)') AS goods_nm	  --보험종류

					, M.Item.query('./insu1_resid').value('.', 'VARCHAR(100)') AS  insu1_resid		--주민등록번호_주피보험자
					, M.Item.query('./insu1_nm').value('.', 'VARCHAR(100)')  AS insu1_nm			--성명_주피보험자
					, M.Item.query('./insu2_resid_1').value('.', 'VARCHAR(100)')  AS insu2_resid_1	--주민등록번호_종피보험자_1
					, M.Item.query('./insu2_nm_1').value('.', 'VARCHAR(100)')  AS insu2_nm_1		--성명_종피보험자_1
					, M.Item.query('./insu2_resid_2').value('.', 'VARCHAR(100)')  AS insu2_resid_2	--주민등록번호_종피보험자_2
					, M.Item.query('./insu2_nm_2').value('.', 'VARCHAR(100)')  AS insu2_nm_2		--성명_종피보험자_2
					, M.Item.query('./insu2_resid_3').value('.', 'VARCHAR(100)')  AS insu2_resid_3	--주민등록번호_종피보험자_3
					, M.Item.query('./insu2_nm_3').value('.', 'VARCHAR(100)')  AS insu2_nm_3		--성명_종피보험자_3
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT								--납입금액계	
					
					,' ' AS SU22	
					,' ' AS SU23
					,' ' AS SU24
					,ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25			
			--INTO #YU01_TB		
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'A102Y','A102M')

UNION ALL
--********************************************************************************
--XML YU02   의료비  "YU02  [의료비]"
--********************************************************************************
			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID					
					,'YU02  [의료비]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd		--서식코드 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid			--주민번호   --이름
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name				--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd			--자료코드     G0003:의료비G0025:의료기기 구입비용G0026:안경 또는 콘택트렌즈 구입비용G0027:보청기 또는 장애인 보장구구입비용
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid			--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm		--상호				
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계		
					
					,' ' AS 	SU12
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'B101Y','B101D')

UNION ALL
--********************************************************************************
--XML YU03   교육비  "YU03  [교육비]"
--********************************************************************************
			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU03  [교육비]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd --서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0004:유초중고(시도교육청),G0006:대학교,G0007:기타(개별제출)
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--학교명
					, M.ITEM.value('./@edu_tp', 'VARCHAR(100)') AS edu_tp		--교육비종류	   1:유치원, 2:초등학교, 3:중학교,4:고등학교, 5:전문대학, 6:대학교,7:대학원, 8:보육시설, C:원격대학,D:학위취득과정, E:시간제과정,F:학원, G:체육시설, H:사회복지시설,J:장애인재활교육기관,K:발달재활서비스제공기관		
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계		
						
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS SU25									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C101Y','C101M' )
UNION ALL
--********************************************************************************
--XML YU04   직업훈련비  "YU04  [직업훈련비]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU04  [직업훈련비]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0005:직업훈련비
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--교육기관명

					, M.Item.query('./course_cd').value('.', 'VARCHAR(100)') AS  course_cd		--과정코드
					, M.Item.query('./subject_nm').value('.', 'VARCHAR(100)') AS  subject_nm	--과정명
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계		
											
											
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25														
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C202Y','C202M' )

UNION ALL
--********************************************************************************
--XML YU05   교복구입비  "YU05  [교복구입비]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU05  [교복구입비]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0024:중고생 교복 구입비용
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT			--납입금액계		
					
					,' ' AS 	SU12
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25			
													
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C301Y','C301M' )

UNION ALL
--********************************************************************************
--XML YU06   개인연금저축  "YU06  [개인연금저축]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU06  [개인연금저축]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0009:개인연금저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호

					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌/증권번호

					, M.Item.query('./start_dt').value('.', 'VARCHAR(100)') AS start_dt		--계약시작일
					, M.Item.query('./end_dt').value('.', 'VARCHAR(100)') AS end_dt		--계약종료일
					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd		--금융회사등 코드
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT			--납입금액계		
						
						
					
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0') AS SU25													
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'D101Y','D101M' )
UNION ALL
--********************************************************************************
--XML YU07   연금저축  "YU07  [연금저축]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU07  [연금저축]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0010:연금저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌/증권번호

					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd					--금융회사등 코드
					, M.Item.query('./ann_tot_amt').value('.', 'VARCHAR(20)') AS ann_tot_amt				--당해연도납입금액
					, M.Item.query('./tax_year_amt').value('.', 'VARCHAR(20)') AS tax_year_amt			--당해연도인출금액
					, M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)')  AS ddct_bs_ass_amt		--순납입금액		
					--, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT								--납입금액계	 (여기에 값이 없음)	
						
					
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)') ,'0') AS SU25   -- ddct_bs_ass_amt		--순납입금액									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'E102Y','E102M' )
UNION ALL
--********************************************************************************
--XML YU08   퇴직연금  "YU08  [퇴직연금]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU08  [퇴직연금]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0011:퇴직연금
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌번호

					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd					--금융회사등 코드
					, M.Item.query('./pension_cd').value('.', 'VARCHAR(100)') AS pension_cd			--계좌유형       11:근로자퇴직급여보장법    12:과학기술인공제

					, M.Item.query('./ann_tot_amt').value('.', 'VARCHAR(20)') AS ann_tot_amt				--당해연도납입금액
					, M.Item.query('./tax_year_amt').value('.', 'VARCHAR(20)') AS tax_year_amt			--당해연도인출금액
					, M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)')  AS ddct_bs_ass_amt		--순납입금액		
					--, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT								--납입금액계	  (XXXXX)	
						
						
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)') ,'0') AS SU25  --ddct_bs_ass_amt		--순납입금액										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'F102Y','F102M' )
UNION ALL
--select * from yu_xml_tb where me_id='2005-0216'

--********************************************************************************
--XML YU09   신용카드  "YU09  [신용카드]"
--********************************************************************************

			--전년도 자료만 땡기기 한다
			SELECT   DISTINCT @DT  AS DT
					, @ME_ID AS ME_ID	
					, 'YU09  [신용카드]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0012:신용카드
					, '' -- M.Item.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, '' --M.Item.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, '' -- M.Item.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)

					, ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도				
					,'0'--, ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계			
						
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					
					, ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도	
					, '0' AS SU25						
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G103Y','G103M' )

UNION ALL
			--귀속년도 자료만 땡기기 한다
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU09  [신용카드]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0012:신용카드
					, M.Item.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.Item.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.Item.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)


					, '0' --M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)') AS first_tot_amt			--전전년도 공제 합계  '2013년도
					, '0' --M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)') AS second_tot_amt		    --전전년도 공제 금액  '2014년도				
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--공제대상금액합계		
						
						
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					, '0' --ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, '0' --ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도				
					, ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G103Y','G103M' )
UNION ALL
--********************************************************************************
--XML YU10   현금영수증  "YU10  [현금영수증]"
--********************************************************************************
			--전년도 자료만 땡기는 부분
			SELECT  DISTINCT    @DT  AS DT
					, @ME_ID AS ME_ID
					,'YU10  [현금영수증]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0013:현금영수증
					--, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					--, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, ''  --M.ITEM.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)
					, '0'  --자릿수 맞추기 위하여
					, '0'  --자릿수 맞추기 위하여

					, M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)') AS first_tot_amt			--전전년도 공제 합계  '2013년도
					, M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)') AS second_tot_amt		    --전전년도 공제 합계  '2014년도				
					, '0' -- M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--공제대상금액합계		
					
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					, ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도				
					, '0' --ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계								
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G204M' )

UNION ALL
			--당해년도만 땡기는 부분입니다.
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU10  [현금영수증]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0013:현금영수증
					--, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					--, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)
					, '0'  --자릿수 맞추기 위하여
					, '0'  --자릿수 맞추기 위하여

					, '0' -- M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)') AS first_tot_amt			--전전년도 공제 합계  '2013년도
					, '0' --M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)') AS second_tot_amt		    --전전년도 공제 합계  '2014년도				
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--공제대상금액합계		
					
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					, '0' --ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, '0' --ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도				
					, ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계								
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G204M' )

UNION ALL
--********************************************************************************
--XML YU11   직불카드  "YU11  [직불카드]"
--********************************************************************************
			SELECT  DISTINCT   @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU11  [직불카드]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0014:직불카드등
					, '' --M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, '' --M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, '' --M.ITEM.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)

					, M.ITEM.value('./@ftyr_tot_amt', 'VARCHAR(100)') AS first_tot_amt			--전전년도 공제 합계  '2013년도
					, M.ITEM.value('./@pre_tot_amt', 'VARCHAR(100)') AS second_tot_amt			--전전년도 공제 합계  '2014년도				
					, '0' --M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--공제대상금액합계		
					
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					, ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도				
					, '0' --ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계												
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G303Y','G303M' )

UNION ALL
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU11  [직불카드]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0014:직불카드등
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)

					, '0' --M.ITEM.value('./@ftyr_tot_amt', 'VARCHAR(100)') AS first_tot_amt			--전전년도 공제 합계  '2013년도
					, '0' --M.ITEM.value('./@pre_tot_amt', 'VARCHAR(100)') AS second_tot_amt			--전전년도 공제 합계  '2014년도				
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--공제대상금액합계		
					
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					, '0' --ISNULL( M.ITEM.value('../@ftyr_tot_amt', 'VARCHAR(100)'),'0') AS SU23  --first_tot_amt			--전전년도 공제 합계  '2013년도
					, '0' --ISNULL( M.ITEM.value('../@pre_tot_amt', 'VARCHAR(100)'),'0') AS SU24  --second_tot_amt		    --전전년도 공제 금액  '2014년도				
					, ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계												
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G303Y','G303M' )

UNION ALL
--********************************************************************************
--XML YU12   주택차입원리금상환  "YU12  [주택차입원리금상환]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU12  [주택차입원리금상환]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0016:주택임차차입금 원리금상환
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)

					, M.Item.query('./goods_nm').value('.', 'VARCHAR(100)') AS goods_nm		--상품명
					, M.Item.query('./lend_dt').value('.', 'VARCHAR(20)') AS lend_dt				--대출일			
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT						--상환액계	
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS SU25  --AMT						--상환액계									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J101Y','J101M' )

UNION ALL
--********************************************************************************
--XML YU13   장기저당차입이자  "YU13  [장기저당차입이자]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU13  [장기저당차입이자]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0017:장기주택저당차입금 이자상환액
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌번호 

					, M.Item.query('./lend_kd').value('.', 'VARCHAR(100)') AS lend_kd					--대출종류     1:무주택자의 중도금 대출2:기존주택구입3:주택분양권 대출
					, M.Item.query('./house_take_dt').value('.', 'VARCHAR(100)') AS house_take_dt		--주택취득일
					, M.Item.query('./mort_setup_dt').value('.', 'VARCHAR(100)') AS mort_setup_dt		--저당권설정일
					, M.Item.query('./start_dt').value('.', 'VARCHAR(100)') AS start_dt					--최초차입일
					, M.Item.query('./end_dt').value('.', 'VARCHAR(100)') AS end_dt						--최종상환예정일
					, M.Item.query('./repay_years').value('.', 'VARCHAR(100)') AS repay_years			--상환기간연수
					, M.Item.query('./lend_goods_nm').value('.', 'VARCHAR(100)') AS lend_goods_nm		--상품명


					, M.Item.query('./debt').value('.', 'VARCHAR(20)')  AS debt							--차입금
					, M.Item.query('./fixed_rate_debt').value('.', 'VARCHAR(20)')  AS fixed_rate_debt		--고정금리차입금
					, M.Item.query('./not_defer_debt').value('.', 'VARCHAR(20)')  AS not_defer_debt		--비거치식상환차입금
					, M.Item.query('./this_year_rede_amt').value('.', 'VARCHAR(20)')  AS his_year_rede_amt	--당해년 원금상환액


					--, M.Item.query('./@ddct').value('.', 'VARCHAR(20)')  AS ddct							--소득공제대상액   --연간합계액	
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS SU23	  --M.ITEM.value('./@ddct', 'VARCHAR(20)') AS use_place_cd	                       --XXXX
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS SU24							--소득공제대상액   --연간합계액	
					--, M.ITEM.value('./@sum', 'VARCHAR(20)') AS use_place_cd								--'XXXXX'  -- 소득공제대상액
					
					,ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0') AS SU25          --소득공제 대상액  --AS use_place_cd	
					    
																
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J203Y','J203M' )

UNION ALL
--********************************************************************************
--XML YU14   주택마련저축  "YU14  [주택마련저축]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU14  [주택마련저축]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0018:주택마련저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS use_place_cd		--계좌번호

					, M.Item.query('./goods_nm').value('.', 'VARCHAR(100)') AS goods_nm			--저축명
					, M.Item.query('./saving_gubn').value('.', 'VARCHAR(100)') AS saving_gubn	--저축구분  1:청약저축,2:주택청약종합저축,4:근로자주택마련저축			
					, M.Item.query('./reg_dt').value('.', 'VARCHAR(100)')  AS reg_dt			--가입일자	
					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)')  AS com_cd			--금융회사등 코드	
					
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--납입금액계	
											
				
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25 -- AMT							--납입금액계					
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J301Y','J301M' )
UNION ALL
--********************************************************************************
--XML YU15   전세이자상환  "YU15  [전세이자상환]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU15  [전세이자상환]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0023:목돈 안드는 전세 이자상환액
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS use_place_cd		--계좌번호

					, M.Item.query('./lend_loan_amt').value('.', 'VARCHAR(100)') AS lend_loan_amt	--대출원금
					, M.Item.query('./lend_dt').value('.', 'VARCHAR(100)') AS lend_dt	--대출일자	
										
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT					--연간합계액	
							
							
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0')  AS 	SU25										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J401Y','J401M' )

UNION ALL
--********************************************************************************
--XML YU15   전세이자상환  "YU16  [소상공인공제부금]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU16  [소상공인공제부금]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0023:목돈 안드는 전세 이자상환액
					--, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					--, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS use_place_cd		--공제계약번호/증서번호

					, M.Item.query('./start_dt').value('.', 'VARCHAR(100)') AS start_dt			--대상기간시작일
					, M.Item.query('./end_dt').value('.', 'VARCHAR(100)') AS end_dt				--대상기간종료일	
					, M.Item.query('./pay_method').value('.', 'VARCHAR(100)') AS pay_method		--납입방법	
										
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT					--납입금액계
					, ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0')
					--, M.ITEM.value('./@ddct', 'VARCHAR(100)') AS ddct					--소득공제대상액		
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0') AS SU25  -- ddct  --	SU25								
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'K101M' )

			--EXEC X_YU_2015_XML;2 '2015','2005-0216','tttt'
UNION ALL
--********************************************************************************
--XML YU17   기부금  "YU17  [기부금]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU17  [기부금]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0023:목돈 안드는 전세 이자상환액
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@donation_cd', 'VARCHAR(100)') AS donation_cd		--기부유형 10:법정기부금,20:정치자금기부금,21:진흥기금출연,40:지정기부금,41:종교단체기부금,42:우리사주기부금 (조특법 제 88조의 4),50:기타기부금

					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT					--납입금액계
							
					,' ' AS 	SU13
					,' ' AS 	SU14		
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS	SU25					
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'L101Y','L101D' )

UNION ALL
--********************************************************************************
--XML YU18   장기증권저축  "YU18  [장기증권저축]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU18  [장기증권저축]' AS  GUBUN
					,'1'   AS SU1
					, 1    AS SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0029:장기집합투자증권저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@secu_no', 'VARCHAR(100)') AS secu_no		--계좌번호


					, M.Item.query('./fund_nm').value('.', 'VARCHAR(100)') AS fund_nm			--펀드명
					, M.Item.query('./reg_dt').value('.', 'VARCHAR(100)') AS reg_dt				--가입일자	
					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd		--금융회사등 코드	
					, M.Item.query('./sum').value('.', 'VARCHAR(20)') AS amt					--연간합계액	
					, M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)')  AS ddct_bs_ass_amt					--소득공제대상금액
									
									
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)') ,'0') AS 	SU25				
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'N101Y','N101M' )


SET NOCOUNT OFF
